Standalone library for working with spreadsheets and CSV in CFML (Lucee and Adobe ColdFusion), supporting all of ColdFusion's native spreadsheet functionality and much more besides.
- Java 8 or higher
- Lucee 5.x or higher
- Adobe ColdFusion 2018 or higher
Note that this is not an extension or package, so does not need to be installed. To use it, simply copy the files/folders to a location where Spreadsheet.cfc can be called by your application code.
The following are the essential files/folders you will need depending on which CFML engine you are using:
helpers/
objects/
lib-osgi.jar
osgiLoader.cfc
Spreadsheet.cfc
helpers/
javaLoader/
lib/
objects/
Spreadsheet.cfc
The following example assumes the file containing the script is in the same directory as the folder containing the spreadsheet library files, i.e.:
- root/
- spreadsheetLibrary/
- Spreadsheet.cfc
- etc.
- script.cfm
- spreadsheetLibrary/
<cfscript>
spreadsheet = New spreadsheetLibrary.Spreadsheet();
data = QueryNew( "First,Last", "VarChar, VarChar", [ [ "Susi", "Sorglos" ], [ "Frumpo", "McNugget" ] ] );
workbook = spreadsheet.new();
spreadsheet.addRows( workbook, data );
spreadsheet.write( workbook, "c:/temp/data.xls" );
</cfscript>
When instantiating the library, the init() method must be called. This will happen automatically if you use the New keyword:
spreadsheet = New spreadsheetLibrary.Spreadsheet();
If using CreateObject() then you must call init() explicitly:
spreadsheet = CreateObject( "component", "spreadsheetLibrary.Spreadsheet" ).init();
You may wish to place the spreadsheet library files in a central location with an application mapping, and instantiate the component using its dot path (e.g. New myLibrary.spreadsheet.Spreadsheet();).
How to create mappings (StackOverflow).
- addAutofilter
- addColumn
- addFreezePane
- addImage
- addInfo
- addPageBreaks
- addRow
- addRows
- addSplitPane
- autosize, implemented as autoSizeColumn
- createSheet
- deleteColumn
- deleteColumns
- deleteRow
- deleteRows
- formatCell
- formatCellRange
- formatColumn
- formatColumns
- formatRow
- formatRows
- getCellComment
- getCellFormula
- getCellValue
- getColumnCount
- info
- isSpreadsheetFile
- isSpreadsheetObject
- mergeCells
- new
- read
- readBinary
- removeSheet
- setActiveSheet
- setActiveSheetNumber
- setCellComment
- setCellFormula
- setCellValue
- setColumnWidth
- setFooter
- setHeader
- setRowHeight
- shiftColumns
- shiftRows
- write
- addConditionalFormatting
- addDataValidation
- addPrintGridlines
- binaryFromQuery
- cleanUpStreamingXml
- clearCell
- clearCellRange
- createCellStyle
- csvToQuery
- download
- downloadCsvFromFile
- downloadFileFromQuery
- getCellAddress
- getCellComments
- getCellFormat
- getCellHyperLink
- getCellType
- getColumnWidth
- getColumnWidthInPixels
- getLastRowNumber
- getPOIVersion
- getPresetColorNames
- getRowCount
- hideColumn
- hideRow
- isBinaryFormat
- isColumnHidden
- isRowHidden
- isStreamingXmlFormat
- isXmlFormat
- newStreamingXlsx
- newXls
- newXlsx
- queryToCsv
- readCsv
- readLargeFile
- removePrintGridlines
- renameSheet
- removeSheetNumber
- setActiveCell
- setCellHyperLink
- setCellRangeValue
- setDateFormats
- setDefaultWorkbookFormat
- setFitToPage
- setFooterImage
- setHeaderImage
- setReadOnly
- setRecalculateFormulasOnNextOpen
- setRepeatingColumns
- setRepeatingRows
- setReturnCachedFormulaValues
- setSheetTopMargin
- setSheetBottomMargin
- setSheetLeftMargin
- setSheetRightMargin
- setSheetHeaderMargin
- setSheetFooterMargin
- setSheetPrintOrientation
- sheetInfo
- showColumn
- showRow
- workbookFromCsv
- workbookFromQuery
- writeFileFromQuery
- writeCsv
- writeToCsv
In Adobe ColdFusion, the SpreadsheetRead() script function is limited to just returning a spreadsheet object, whereas the <cfspreadsheet action="read"> tag has a range of options for reading and returning data from a spreadsheet file.
The read() method in this library allows you to read a spreadsheet file into a query and return that instead of a spreadsheet object. It includes all of the options available in <cfspreadsheet action="read">.
<cfscript>
myQuery = spreadsheet.read( src=mypath, format="query" );
</cfscript>
The read() method also features the following additional options not available in ColdFusion or the Spreadsheet Extension:
fillMergedCellsWithVisibleValueincludeHiddenColumnsincludeRichTextFormattingpasswordto open encrypted spreadsheetscsvDelimiterqueryColumnTypes
From version 3, multiple calls can be chained together, simplifying your code into a more expressive syntax.
spreadsheet.newChainable( "xlsx" )
.addRows( data )
.formatRow( { bold: true }, 1 )
.write( filepath );
The following international date masks are used by default to read and write cell values formatted as dates:
- DATE =
yyyy-mm-dd - TIME =
hh:mm:ss - TIMESTAMP =
yyyy-mm-dd hh:mm:ss
An additional mask is used to output datetime values from the read() method into HTML or CSV formats:
- DATETIME =
yyyy-mm-dd HH:nn:ss
NB: Do not confuse DATETIME and TIMESTAMP. In general you should override the TIMESTAMP mask.
Each of these can be overridden by passing in a struct including the value(s) to be overridden when instantiating the Spreadsheet component. For example:
<cfscript>
spreadsheet = New spreadsheetLibrary.Spreadsheet( dateFormats={ DATE: "mm/dd/yyyy" } );
</cfscript>
Or by using the setDateFormats() method on an existing instance.
<cfscript>
spreadsheet = New spreadsheetLibrary.Spreadsheet();
spreadsheet.setDateFormats( { DATE: "mm/dd/yyyy" } );
</cfscript>
While the above will set the library defaults, you can format cells with specific masks using the dataFormat attribute which can be passed to formatCell and the other formatting methods, as part of the format argument:
// display datetime value with millisecond precision
spreadsheet.formatColumn( workbook , { dataformat: "yyyy-mm-dd hh:mm:ss.000" }, 1 );
From version 2.14.0, Lucee loads the POI and other required java libraries using OSGi. This is not yet supported with Adobe ColdFusion which by default uses an included version of Mark Mandel's JavaLoader.
For more details and options see: Loading the POI java libraries
You can also download this library through CommandBox/Forgebox.
box install spreadsheet-cfml
It will download the files into a modules directory and can be used just the same as downloading the files manually.
If using ColdBox you can use either of the WireBox bindings like so:
spreadsheet = wirebox.getInstance( "Spreadsheet@spreadsheet-cfml" );
spreadsheet = wirebox.getInstance( "Spreadsheet CFML" );
The automated tests require TestBox 5.0 or later. You will need to create an application mapping for /testbox
The code was originally adapted from the work of TeamCfAdvance. Ben Nadel's POI Utility was also used as a basis for parts of the read functionality. Header/Footer image functionality is based on code by Axel Richter.
JavaLoader is by Mark Mandel.
Copyright (c) 2015-2024 Julian Halliwell
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
